<?php
/**
 * ActiveRecord Finder.
 *
 * @package    XDao Framework
 * @author     Cristian Buda <hello@phalien.com>
 * @author     Bogdan Stanescu <bstanescu@gmail.com>
 * @copyright  (c) 2011
 */
class XDao_Finder
{
	// Table schema
	public $schema;

	// Active joins
	public $joins;

	// SQL conditions
	protected $sql_conditions;

	/**
	 * Stores table schema.
	 *
	 * @return void
	 */
	public function __construct($schema)
	{
		$this->schema = $schema;
	}
	
	/**
	 * Instantiate an empty item for current table.
	 *
	 * @return  object
	 */
	public function fetch_new()
	{
		if (isset($this->schema->item))
		{
			$item = new $this->schema->item($this->schema);
		}
		else
		{
			$item = new XDao_Item($this->schema);
		}
		return $item;
	}	
	
	/**
	 * Returns all items that match current conditions.
	 *
	 * @return array
	 */
	public function all()
	{
		$response = new XDao_List();
		if ($rs = $this->schema->db->Query($this->build_query()))
		{
			foreach ($rs as $row)
			{
				$item = $this->fetch_new();
				$item->populate($row);
				
				if ($this->joins)
				{
					$item = array($item->schema->table => $item);
					foreach ($this->joins as $relation)
					{
						if (is_array($relation))
						{
							$table = new $this->schema->relations[$relation[0]][XDb::REL_CLASS];
							$classname = $table->schema()->relations[$relation[1]][XDb::REL_CLASS];
							$table2 = new $classname;
							$join_item = $table2->fetch_new();
						}
						else
						{
							$table = new $this->schema->relations[$relation][XDb::REL_CLASS];
							$join_item = $table->fetch_new();
						}
						
						$join_item->populate($row);
						$item[$join_item->schema->table] = $join_item;
					}
				}
				
				$response[] = $item;
			}
		}

		$this->joins = NULL;

		return $response;
	}


	/**
	 * Delete all items that match current conditions.
	 *
	 * @return array
	 */
	public function delete()
	{
		return $this->schema->db->Execute($this->build_query(FALSE, TRUE));
	}
	
	/**
	 * Build the SQL query based on all set conditions.
	 *
	 * @param   boolean  indicates whether to build a count query
	 * @param   boolean  indicates whether the query SELECTS or DELETES
	 * @return  string
	 */
	public function build_query($count_rows = FALSE, $delete = FALSE)
	{
		if ($delete)
		{
			$sql = 'DELETE';			
		}
		{
			$sql = 'SELECT';
		
			// Columns to retrieve
			if ($count_rows)
			{
				$sql .= ' COUNT(*) ';
			}
			else
			{
				$sql .= isset($this->sql_conditions['select']) ? ' ' . implode(', ', $this->sql_conditions['select']) . ' ' : ' ' . $this->schema->table . '.* ';
			}
		}
		
		$sql .= ' FROM ' . $this->schema->table;

		// External joins
		if (isset($this->sql_conditions['external_join']))
		{
			foreach ($this->sql_conditions['external_join'] as $join)
			{
				$sql .= ' LEFT JOIN ' . $join[XDb::JOIN_TABLE] . ' ON ';
				foreach ($join[XDb::JOIN_CONDITIONS] as $condition)
				{
					$sql .= $condition[XDb::JOIN_COND_LOGIC] . ' (' . $condition[XDb::JOIN_COND_OWN_KEY] . ' ' . $condition[XDb::JOIN_COND_OPERATION] . ' ' . $condition[XDb::JOIN_COND_KEY] . ') ';
				}
			}
		}
		
		// Joins with related tables
		if (isset($this->sql_conditions['join']))
		{
			foreach ($this->sql_conditions['join'] as $relation)
			{
				if (is_array($relation))
				{
					$table = new $this->schema->relations[$relation[0]][XDb::REL_CLASS];
					$class_name = $table->schema()->relations[$relation[1]][XDb::REL_CLASS];
					$table2 = new $class_name;
					
					$schema1 = $table->schema();
					$schema2 = $table2->schema();
					
					$rel_type = $schema1->relations[$relation[0]][XDb::REL_TYPE];
				}
				else
				{
					$table = new $this->schema->relations[$relation][XDb::REL_CLASS];
					
					$schema1 = $this->schema;
					$schema2 = $table->schema();

					$rel_type = $schema1->relations[$relation][XDb::REL_TYPE];
				}
				
				switch ($rel_type)
				{
					case XDb::BELONGS_TO:
					{
						$own_key = isset($schema1->relations[$relation][XDb::REL_OWN_KEY]) 
							? $schema1->relations[$relation][XDb::REL_OWN_KEY] : $schema2->columns[$schema2->primary]->name;

						$foreign_key = isset($schema1->relations[$relation][XDb::REL_KEY]) 
							? $schema1->relations[$relation][XDb::REL_KEY] : $schema2->columns[$schema2->primary]->name;

						break;
					}
					case XDb::HAS_ONE:
					{
						$own_key = isset($schema1->relations[$relation][XDb::REL_OWN_KEY]) 
							? $schema1->relations[$relation][XDb::REL_OWN_KEY] : $schema1->columns[$schema1->primary]->name;

						$foreign_key = isset($schema1->relations[$relation][XDb::REL_KEY]) 
							? $schema1->relations[$relation][XDb::REL_KEY] : $schema1->columns[$schema1->primary]->name;

						break;
					}
				}

				$sql .= ' LEFT JOIN ' . $schema2->table . ' ON ' . $schema1->table . '.' . $own_key . ' = ' . $schema2->table . '.' . $foreign_key . ' ';


			}
		}

		// Filter the result set
		if (isset($this->sql_conditions['where_sql']))
		{
			$sql .= ' WHERE ' . $this->parse_where($this->sql_conditions['where_sql']);
		}
		elseif (isset($this->sql_conditions['where']))
		{
			$sql_contains_functions = false;
			foreach ($this->sql_conditions['where'] as $idx => $where)
			{
				$sql .= ($idx) ? ' AND ' : ' WHERE ';
				
				// if first parameter contains functions
				if (strpos($where[0], '(') !== false)
				{
					$param = preg_replace('/([(,])\h*([a-z0-9_]+)\h*([^(),a-z0-9_])*([),])/is', '$1---$2---$3$4', $where[0]);
					$param = preg_replace('/\h*([a-z0-9_]+)\h*([),])/is', '---$1---$2', $param);
					foreach($this->schema->columns as $key => $column)
					{
						$where[0] = str_replace('---' . $key . '---', $column->name, $param);
					}
					$where[0] = preg_replace('/---([a-z0-9_]+)---/is', '$1' , $where[0]);					
					$sql .= $where[0] . ' ' . $where[1] . ' ';
					$sql_contains_functions = true;
				}
				else
				{
					$sql .= $this->schema->table . '.' . $this->schema->columns[$where[0]]->name . ' ' . $where[1] . ' ';
				}
				
				
				switch ($where[1])
				{
					case 'IN':
					{
						foreach ($where[2] as $key => $value)
						{
							if ($where[3])
							{
								$where[2][$key] = $value;
							}
							else
							{
								$where[2][$key] = $this->schema->db->Escape($value, $this->schema->columns[$where[0]]->type);
							}
						}
						$sql .= '(' . implode(', ', $where[2]) . ')';
						break;
					}
					case 'BETWEEN':
					{
						foreach ($where[2] as $key => $value)
						{
							if ($where[3])
							{
								$where[2][$key] = $value;
							}
							else
							{
								$where[2][$key] = $this->schema->db->Escape($value, $this->schema->columns[$where[0]]->type);
							}
						}
						$sql .= $where[2][0] . ' AND ' . $where[2][1];
						break;
					}
					default:
					{
						if ($where[3])
						{
							$sql .= $where[2];
						}
						else
						{
							if ($sql_contains_functions)
							{
								if (is_string($where[2]))
								{
									$type = 'text';
								}								
							}
							else
							{
								$type = $this->schema->columns[$where[0]]->type;
							}
							$sql .= $this->schema->db->Escape($where[2], $type);
						}
					}
				}
			}
		}

		// Order the result set
		if (isset($this->sql_conditions['order_by']))
		{			
			$order_sql_arr = array();
			foreach ($this->sql_conditions['order_by'] as $order)
			{				
				if (isset($this->schema->columns[$order['column']]))
				{
					$order_sql = $this->schema->columns[$order['column']]->name;
				}
				else
				{
					$order_sql = $order['column'];					
				}
				if (!empty($order['direction']))
				{
					$order_sql .= ' ' . $order['direction'];
				}
				$order_sql_arr[] = $order_sql;
			}
			$sql .= ' ORDER BY ' . implode(',', $order_sql_arr);
		}
		
		// Limit the result set
		if (isset($this->sql_conditions['limit_offset']) && isset($this->sql_conditions['limit_count']))
		{
			$sql .= ' LIMIT ' . intval($this->sql_conditions['limit_offset']) . ', ' . intval($this->sql_conditions['limit_count']);
		}
		
		// Clears conditions
		$this->clear();

		return $sql;
	}

	/**
	 * Clears sql conditions.
	 * 
	 * @return object
	 */
	protected function clear()
	{
		$this->joins = $this->sql_conditions['join'];
		$this->sql_conditions = NULL;
		return $this;
	}

	/**
	 * Returns the count of curent result set based on set conditions.
	 *
	 * @return integer
	 */
	public function count()
	{
		$sql = $this->build_query(XDb::COUNT_ROWS);
		$count = $this->schema->db->QueryItem($sql);
		return $count;
	}

	/**
	 * Returns first item that matches current conditions.
	 *
	 * @return object
	 */
	public function first()
	{
		if ($row = $this->schema->db->QueryRow($this->build_query()))
		{
			$item = $this->fetch_new();
			$item->populate($row);

			if ($this->joins)
			{
				$item = array($item->schema->table => $item);
				foreach ($this->joins as $relation)
				{
					if (is_array($relation))
					{
						$table = new $this->schema->relations[$relation[0]][XDb::REL_CLASS];
						$classname = $table->schema()->relations[$relation[1]][XDb::REL_CLASS];
						$table2 = new $classname;
						$join_item = $table2->fetch_new();
					}
					else
					{
						$table = new $this->schema->relations[$relation][XDb::REL_CLASS];
						$join_item = $table->fetch_new();
					}
					$join_item->populate($row);
					$item[$join_item->schema->table] = $join_item;
				}
			}
			

			return $item;
		}

		$this->joins = null;

		return FALSE;
	}

	/**
	 * Selector: Limits the query to a subset of items.
	 *
	 * @param   integer  offset
	 * @param   integer  items count
	 * @return  object
	 */
	public function limit($offset, $count)
	{
		$this->sql_conditions['limit_offset'] = $offset;
		$this->sql_conditions['limit_count'] = $count;
		return $this;
	}

	/**
	 * Selector: Orders the result set.
	 *
	 * @param   string  column nice name
	 * @param   string  direction (ASC or DESC)
	 * @return  object
	 */
	public function orderby($column, $direction = 'ASC')
	{
		if (!isset($this->sql_conditions['order_by']))
		{
			$this->sql_conditions['order_by'] = array();
		}
		$order_by = array('column' => $column, 'direction' => $direction);
		$this->sql_conditions['order_by'][] = $order_by;
		return $this;
	}

	/**
	 * Recursively parse a query given as array and generates SQL where clause.
	 *
	 * @param   array   query to parse
	 * @param   string  the operation between subqueries
	 * @return  string  SQL where clause
	 */
	private function parse_where($query, $logic = 'and', $depth = 1)
	{
		$response = array();

		// Tweak to allow simple queries like $query = array('col', '=', 'val')
		if ($depth == 1 && count($query) == 3)
		{
			if (!(array_key_exists('or', $query) || array_key_exists('and', $query))) $query = array($query);
		}

		$query = array_change_key_case($query);
		
		// Take each subquery
		foreach ($query as $key => $subquery)
		{
			// If current subquery has subqueries, recursively parse them
			if (in_array(strval($key), array('and', 'or')))
			{
				$sql = $this->parse_where($subquery, $key, $depth + 1);
			}
			else
			{
				// Check if column name is valid
				if (!array_key_exists($subquery[XDb::SQL_COLUMN], $this->schema->columns))
				{
					XDao_Model::trigger_error(XDao_Model::ERR_COLUMN_NON_EXISTING, $subquery[XDb::SQL_COLUMN]);
				}
				// Build query
				$sql = $this->schema->columns[$subquery[XDb::SQL_COLUMN]]->name . ' ' . $subquery[XDb::SQL_OPERATION] . ' ' . 
					$this->schema->db->Escape($subquery[XDb::SQL_VALUE], $this->schema->columns[$subquery[XDb::SQL_COLUMN]]->type);
			}

			$response[] = $sql;
		}

		return '(' . implode(' ' . $logic . ' ', $response) . ')';
	}

	/**
	 * Selector: Indicates what columns to retrive.
	 * It used mostly for internal methods and shouldn't be combined with all() or first() !!
	 * Can be combined safely with to_array()
	 *
	 * @param   string  column nice name
	 * @param   string  column value
	 * @param   string  operation
	 * @return  object
	 */
	public function select()
	{
		if ($args = func_get_args())
		{
			foreach ($args as $arg)
			{
				$this->sql_conditions['select'][] = array_key_exists($arg, $this->schema->columns)
					? $this->schema->columns[$arg]->name : $arg;
			}
		}
		return $this;
	}

	/**
	 * Returns all items that match current conditions as an associative array.
	 *
	 * @return array
	 */
	public function to_array()
	{
		$response = array();
		if ($rs = $this->schema->db->Query($this->build_query()))
		{
			foreach ($rs as $row)
			{
				$item = array();
				foreach ($row as $column => $value)
				{
					$key = isset($this->schema->reverse_columns[$column]) ? $this->schema->reverse_columns[$column] : $column;
					if (!isset($item[$key]))
					{
						$item[$key] = $value;
					}
				}
				$response[] = $item;
			}
		}
		return $response;
	}

	/**
	 * Selector: Filters the query by a column value.
	 * This selector can't be combined with where_query()
	 *
	 * @param   string   column nice name
	 * @param   string   operation
	 * @param   string   column value
	 * @param   boolean  escapes or not given values
	 * @return  object
	 */
	public function where($column, $operation, $value, $no_escape = FALSE)
	{
		switch (strtoupper($operation))
		{
			case 'IN':
			{
				if (!is_array($value))
				{
					XDao_Model::trigger_error(XDao_Model::ERR_ARRAY_EXPECTED);
				}
				break;
			}
			case 'BETWEEN':
			{
				if (!(is_array($value) && count($value) == 2)) 
				{
					XDao_Model::trigger_error(XDao_Model::ERR_ARRAY_PAIR_EXPECTED);
				}
				break;
			}
		}
		$this->sql_conditions['where'][] = array($column, strtoupper($operation), $value, $no_escape);
		return $this;
	}

	/**
	 * Selector: Filters the query with a complex query.
	 * This selector can't be combined with where()
	 *
	 * @example  $ar = new Companies; $companies = $ar->where_query(array('name', 'LIKE', 'blue%'));
	 * @example  $ar = new Companies; $companies = $ar->where_query(array('or' => array(array('name', 'LIKE', 'b%'), array('name', 'LIKE', 'a%'))));
	 *
	 * @param   array   array with query
	 * @return  object
	 */
	public function where_query($query)
	{
		$this->sql_conditions['where_sql'] = $query;
		return $this;
	}
	
	public function join($relation)
	{
		$this->sql_conditions['join'][] = $relation;
		return $this;
	}

	public function join_external($table, $alias, $conditions)
	{
		$this->sql_conditions['external_join'][] = array($table, $alias, $conditions);
		return $this;
	}
		
}

?>